A database is a collection of stored data that is logically related and that is accessible by one or more users. A popular type of database is the relational database management system, which includes relational tables made up of rows and columns. Each row represents an occurrence of an entity defined by the table, with an entity being a person, place, or thing about which the table contains information. Various types of operations can be performed in the database system.
In one arrangement, to extract data from a relational table, Structured Query Language (SQL) statements can be issued to the database system. In response to SQL statements, data is extracted from one or more tables in the database system. SQL statements include data definition language (DDL) statements that are used to define database structures and data manipulation language (DML) statements that manipulate rows and data values. Examples of DML statements include INSERT, SELECT, UPDATE, and DELETE.
Concurrent access to data for read-only queries is generally allowed. However, an update transaction typically blocks all subsequent reads of a table or a row in the table, or vice versa, an update transaction is blocked by all outstanding conflicting reads. Locking prevents multiple users from violating data integrity. Typically, locks are acquired during the processing of a request or transaction and released at the termination of the request or transaction.
Various lock granularity levels are possible, including database, table, hash range, hash, and row locks. A database lock causes all tables in the database to be locked, while a table lock causes all rows in the table to be locked. A hash range lock causes a range of hash values to be locked, while a hash lock specifies a hash value to lock. A row lock locks a specific row in a table. Within each lock granularity, several different levels of locks are possible, including exclusive locks, write locks, read locks, and access locks. An exclusive lock is the most restrictive type of lock, as all other requests are locked out. A write lock enables a request to modify data while locking out all other requests except read requests not concerned about data consistency. A read lock is used to ensure consistency during read operations; several requests may hold a concurrent read lock on the same data, during which no modification of the data is permitted. Typically, users who are not concerned about data consistency can specify access locks. Using an access lock allows for reading of data while modification of the data is in progress.
In many conventional systems, the number of locks that can be held at a given time can be limited. Additionally, even if more locks are provided, having a large number of locks concurrently active at a given time can adversely affect system performance. Consequently, in some systems, table-level locks are generally used to reduce the-number of outstanding locks at any given time. However, a table-level lock can prevent other updates from proceeding, even though the other updates involve different portions of the table.
In general, a lock mechanism and method is provided to enable the efficient provision of a lower level of locking granularity when needed. For example, a database system comprises a storage module to store a table, the table containing portions of data and one or more indications to indicate corresponding one or more of the data portions being modified. A controller is adapted to perform lock management using the one or more indications contained in the table.
According to another example, a database system comprises a storage module containing a table having plural portions and a lock manager adapted to place a table-level lock on the table for a first transaction. The first transaction updates a first portion of the table. The lock manager is adapted to further establish a lower level lock on the first table portion in response to a second transaction requesting an access of the first portion of the table.